[CHAPTER 01. PostgreSQL Wait Interface]

2. PostgreSQL에서의 Wait
2-2. PostgreSQL에서의 Wait Event(ver.9.6~)

/* pg_wait_events */
SELECT   *
FROM     pg_wait_events;


2-3. PostgreSQL Wait Monitoring

/* pg_locks */
SELECT   pid,
         locktype,
         CASE locktype
             WHEN 'relation'      THEN relation::regclass::text
             WHEN 'transactionid' THEN transactionid::text
             WHEN 'virtualxid'    THEN virtualxid
             WHEN 'tuple'         THEN relation::regclass::text||'('||page||','||tuple||')'
             WHEN 'page'          THEN relation::regclass::text||'('||page||')'
         END AS lockid,
         mode,
         granteda
FROM     pg_locks
ORDER BY pid,
         locktype,
         lockid;

SELECT   pl.pid,
         pl.locktype,
         nsp.nspname          AS schema_name,
         pc.relname           AS object_name,
         pc.relkind           AS object_type,
         pc.relowner          AS owner_oid,
         pc.relfilenode       AS relfilenode,
         pl.mode,
         pl.granted
FROM     pg_locks pl
JOIN     pg_class pc
         ON pl.relation = pc.oid
JOIN     pg_namespace nsp
         ON pc.relnamespace = nsp.oid
ORDER BY pl.pid,
         nsp.nspname,
         pc.relname;         

/* pg_stat_activity */         
SELECT   pid, 
         usename, 
         state, 
         wait_event_type, 
         wait_event,
         pg_blocking_pids(pid) AS blocking
FROM     pg_stat_activity;         

SELECT   blocked_locks.locktype,              
         blocked_activity.pid AS blocked_pid, 
         blocked_activity.query AS blocked_query, 
         blocked_locks.mode,                
         blocking_activity.pid AS blocking_pid,   
         blocking_activity.query AS blocking_query 
FROM     pg_locks blocked_locks
JOIN     pg_stat_activity blocked_activity
         ON ked_locks.pid = blocked_activity.pid
JOIN     pg_locks blocking_locks
         ON (blocking_locks.transactionid IS NOT DISTINCT FROM blocked_locks.transactionid
             AND blocking_locks.database IS NOT DISTINCT FROM blocked_locks.database
             AND blocked_locks.pid != blocking_locks.pid)
JOIN     pg_stat_activity blocking_activity
         ON blocking_locks.pid = blocking_activity.pid
WHERE    NOT blocked_locks.granted;

SELECT   pid, 
         locktype, 
         transactionid, 
         mode, 
         granted, 
         waitstart
FROM     pg_locks
WHERE    locktype = 'transactionid';

/* Extension */
SELECT   *
FROM     pg_available_extensions;

CREATE EXTENSION pg_wait_sampling;
CREATE EXTENSION pg_stat_statements;

/* pg_wait_sampling */
SELECT   *
FROM     pg_wait_sampling_history;

SELECT   *
FROM     pg_wait_sampling_profile
WHERE    pid = 4160596;

SELECT   pss.query,
         pwsp.event_type,
         pwsp."event",
         SUM(pwsp.count)
FROM     pg_wait_sampling_profile pwsp
JOIN     pg_stat_statements pss
  ON     pwsp.queryid = pss.queryid
WHERE    pwsp.event_type <> 'Client'
GROUP BY pss.query,
         pwsp.event_type,
         pwsp."event"
ORDER BY SUM(pwsp.count) DESC;

/* pageinspect */
CREATE EXTENSION pageinspect;

SELECT   *
FROM     heap_page_items(get_raw_page('heap_test', 0));

/* pgrowlocks */
CREATE EXTENSION pgrowlocks;

SELECT   *
FROM     pgrowlocks('row_test');

/* pg_lw_lock_tracer */
pg_lw_lock_tracer -p 509470 -v --statistics >> a.log
